﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text
Imports System.IO
<System.ComponentModel.DataObjectAttribute(True)> _
Partial Public Class BLL_ManageRoleProgramMapping
    Public Sub BLL_ManageRoleProgramMapping()
        '
        ' TODO: 在此加入建構函式的程式碼
        ''
    End Sub
    'SELECT
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)> _
    Public Function BLL_Select(ByVal RoleID As Integer, ByVal Parent As Integer) As DataTable
        Dim dat As New clsData
        Dim sql As New MyStringBuilder
        Dim sqlParams As SqlParameter() = New SqlParameter(1) {}


        sqlParams(0) = New SqlParameter("@RoleID", SqlDbType.Int)
        sqlParams(0).Value = RoleID
        sqlParams(1) = New SqlParameter("@Parent", SqlDbType.Int)
        sqlParams(1).Value = Parent

        sql.AppendFormat("SELECT A.ProgramID,A.ProgramName,A.Parent,A.ChkVisible ")
        sql.AppendFormat(",Case when C.ReadOnly is null then isnull(B.ReadOnly,0) else C.ReadOnly end as ReadOnly ")
        sql.AppendFormat(",Case when C.LimitAccess is null then isnull(B.LimitAccess,0) else C.LimitAccess end as LimitAccess ")
        sql.AppendFormat(",CASE WHEN B.ProgramID is null THEN (CASE WHEN C.ProgramID is null THEN '0' ELSE '1' END) ELSE '1' END as FullAccess ")
        sql.AppendFormat(",CASE WHEN C.ProgramID is null THEN '1' ELSE '0' END as IsInherits ")
        sql.AppendFormat("FROM Program A ")
        sql.AppendFormat("inner join (SELECT * FROM dbo.fn_GetAllSonsByProgramID(@Parent)) Papa on A.ProgramID=Papa.[Value] ")
        sql.AppendFormat("left join RoleProgramMapping B on A.ProgramID=B.ProgramID AND B.RoleID=@RoleID ")
        sql.AppendFormat("left join RoleProgramMapping C on A.ProgramID=C.ProgramID AND C.RoleID=(SELECT Parent FROM Role WHERE RoleID=@RoleID) ")
        sql.AppendFormat("WHERE A.Active='Y' AND A.Editable='Y' ")
        sql.AppendFormat("ORDER BY Papa.SN; ")
        Dim DS As DataSet = dat.GetData(sql.ToString, sqlParams, Data.CommandType.Text, "dt")

        If DS IsNot Nothing AndAlso DS.Tables.Count > 0 Then
            Dim dt As DataTable = DS.Tables(0)
            For i As Integer = 0 To dt.Rows.Count - 1
                If dt.Rows(i)("ReadOnly") <> "0" OrElse dt.Rows(i)("LimitAccess") <> "0" Then
                    dt.Rows(i)("FullAccess") = "0"
                End If
            Next
            Return DS.Tables(0)
        Else
            Return Nothing
        End If
    End Function
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Delete)> _
    Public Function BLL_Delete(ByVal original_RoleID As Integer, ByVal original_ProgramID As Integer) As DataTable
        Dim dat As New clsData
        Dim sql As New MyStringBuilder
        Dim sqlParams As SqlParameter() = New SqlParameter(1) {}


        sqlParams(0) = New SqlParameter("@RoleID", SqlDbType.Int)
        sqlParams(0).Value = original_RoleID
        sqlParams(1) = New SqlParameter("@ProgramID", SqlDbType.Int)
        sqlParams(1).Value = original_ProgramID



        sql.AppendFormat("DELETE RoleProgramMapping ")
        sql.AppendFormat("WHERE RoleID=@RoleID AND ProgramID=@ProgramID ")

        Dim DS As DataSet = dat.GetData(sql.ToString, sqlParams, Data.CommandType.Text, "dt")

        If DS IsNot Nothing AndAlso DS.Tables.Count > 0 Then

            Return DS.Tables(0)

        Else

            Return Nothing

        End If

    End Function
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Insert)> _
    Public Function BLL_Insert(ByVal RoleID As Integer, ByVal ProgramID As Integer) As DataTable
        Dim dat As New clsData
        Dim sql As New MyStringBuilder
        Dim sqlParams As SqlParameter() = New SqlParameter(1) {}


        sqlParams(0) = New SqlParameter("@RoleID", SqlDbType.Int)
        sqlParams(0).Value = RoleID
        sqlParams(1) = New SqlParameter("@ProgramID", SqlDbType.Int)
        sqlParams(1).Value = ProgramID

        sql.AppendFormat("Insert RoleProgramMapping(RoleID,ProgramID,AuthName) ")
        sql.AppendFormat("SELECT  ")
        sql.AppendFormat("@RoleID,@ProgramID,@RoleID ")

        Dim DS As DataSet = dat.GetData(sql.ToString, sqlParams, Data.CommandType.Text, "dt")

        If DS IsNot Nothing AndAlso DS.Tables.Count > 0 Then

            Return DS.Tables(0)

        Else

            Return Nothing

        End If
    End Function
End Class
